﻿CREATE procedure [dbo].[spReportWeeklyClientVariance]
	@yearPosition int,
	@WeekNumber int,
	@Month int
AS

DECLARE @year int
SET @year = YEAR(GETDATE()) + @yearPosition
set ansi_nulls OFF

DECLARE @MondayDate date
SELECT @MondayDate = DATEADD(DAY, -DATEPART(weekday, GETDATE()) + 2, CONVERT(date, GETDATE()))

DECLARE @weekNumberMonday smallint
SET @weekNumberMonday = datepart(day, datediff(day, 0, @MondayDate)/7 * 7)/7 + 1

SET @MondayDate = DATEADD(DAY, (@WeekNumber - @weekNumberMonday - 1) * 7, @MondayDate)

DECLARE @query nvarchar(max)

SET @query = 'SELECT PeriodYear as [Period Year]
	, ProgramId as [Program Id]
	, ProgramName as [Program Name]
	, ProgramManager as [Program Manager]
	, ProjectId as [Project Id]
	, ProjectName as [Project Name]
	, BowFlag as [Bow Flag]
	, ClientLevel5 as [Client Level 5]	
	, ClientLevel6 as [Client Level 6]
	, ClientLevel7 as [Client Level 7]
	, ClientGocCode as [Client GOC Code]
	, LobParent as [Lob Parent]
	, ISNULL(PLFY0, 0) as [$ P&L FY]
	, ISNULL(Allocation0, 0) as [% Allocation]

	, ISNULL(PLFY1, 0) as [ $ P&L FY]
	, ISNULL(Allocation1, 0) as [ % Allocation]
	, ISNULL(Allocation1 - Allocation0, 0) as [ Variance]

	, ISNULL(PLFY2, 0) as [ $ P&L FY ]
	, ISNULL(Allocation2, 0) as [ % Allocation ]
	, ISNULL(Allocation2 - Allocation1, 0) as [ Variance ]

	, ISNULL(PLFY3, 0) as [ $ P&L FY  ]
	, ISNULL(Allocation3, 0) as [ % Allocation  ]
	, ISNULL(Allocation3 - Allocation2, 0) as [ Variance  ]

	, ISNULL(PLFY4, 0) as [ $ P&L FY   ]
	, ISNULL(Allocation3, 0) as [ % Allocation   ]
	, ISNULL(Allocation4 - Allocation3, 0) as [ Variance   ]

	, ISNULL(PLFY5, 0) as [  $ P&L FY   ]
	, ISNULL(Allocation5, 0) as [  % Allocation   ]
	, ISNULL(Allocation5 - Allocation4, 0) as [  Variance   ]
from BookOfWorkClientVariance
Where PeriodYear = ' + CONVERT(varchar(4), @year) + '
 AND Month = ' + CONVERT(varchar(2), @Month) + '
 AND ReferenceDate = ''' + CONVERT(varchar(100),@MondayDate) + ''''

EXEC (@query)